﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient

Public Class KhachThueDAO
    Public Shared Function GetAll() As List(Of KhachThueDTO)
        Dim listKhachThue As New List(Of KhachThueDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectKHACHTHUEsAll"
            cmd.Connection = cn
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim khachthue As KhachThueDTO = New KhachThueDTO()
                khachthue.strMaKhachThue = rd.GetString(0)
                khachthue.strHoTen = rd.GetString(1)
                khachthue.strDienThoai = rd.GetString(2)
                khachthue.isTinhTrang = rd.GetBoolean(3)
                khachthue.isTamXoa = rd.GetBoolean(4)

                listKhachThue.Add(khachthue)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listKhachThue
    End Function
    Public Shared Function GetAllKhachTamXoa() As List(Of KhachThueDTO)
        Dim listKhachThue As New List(Of KhachThueDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectKHACHTHUETamXoa"
            cmd.Connection = cn
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim khachthue As KhachThueDTO = New KhachThueDTO()
                khachthue.strMaKhachThue = rd.GetString(0)
                khachthue.strHoTen = rd.GetString(1)
                khachthue.strDienThoai = rd.GetString(2)

                listKhachThue.Add(khachthue)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listKhachThue
    End Function
    Public Shared Function ExportDS_KhachThue() As DataTable
        Dim listKhachThue As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "exportDS_KhachThue"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listKhachThue)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
        Return listKhachThue
    End Function
    

    Public Shared Function GetByID(ByVal sqlParameter As SqlParameter) As List(Of KhachThueDTO)
        Dim listKhachThue As New List(Of KhachThueDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectKhachthue"
            cmd.Connection = cn
            If Not IsNothing(sqlParameter) Then
                cmd.Parameters.Add(sqlParameter)
            End If
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim khachthue As KhachThueDTO = New KhachThueDTO()
                khachthue.strMaKhachThue = rd.GetString(0)
                khachthue.strHoTen = rd.GetString(1)
                khachthue.strDienThoai = rd.GetString(2)
                khachthue.isTinhTrang = rd.GetBoolean(3)
                khachthue.isTamXoa = rd.GetBoolean(4)

                listKhachThue.Add(khachthue)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listKhachThue
    End Function

    Public Shared Function LayDSMaKhach() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayDSMaKhach"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

            da.Fill(dt)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return dt
    End Function
    Public Shared Function TimKiemTheoTen(ByVal sqlParameter As SqlParameter) As List(Of KhachThueDTO)
        Dim listKhachThue As New List(Of KhachThueDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKhachThueTheoTen"
            cmd.Connection = cn
            If Not IsNothing(sqlParameter) Then
                cmd.Parameters.Add(sqlParameter)
            End If
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim khachthue As KhachThueDTO = New KhachThueDTO()
                khachthue.strMaKhachThue = rd.GetString(0)
                khachthue.strHoTen = rd.GetString(1)
                khachthue.strDienThoai = rd.GetString(2)
                khachthue.isTinhTrang = rd.GetBoolean(3)
                khachthue.isTamXoa = rd.GetBoolean(4)

                listKhachThue.Add(khachthue)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listKhachThue
    End Function

    Public Shared Function TimKiemTheoNhuCauNha(ByVal sqlParameter As SqlParameter) As List(Of KhachThueDTO)
        Dim listKhachThue As New List(Of KhachThueDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "TimKhachTheoNhuCauNha"
            cmd.Connection = cn
            If Not IsNothing(sqlParameter) Then
                cmd.Parameters.Add(sqlParameter)
            End If
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim khachthue As KhachThueDTO = New KhachThueDTO()
                khachthue.strMaKhachThue = rd.GetString(0)
                khachthue.strHoTen = rd.GetString(1)
                khachthue.strDienThoai = rd.GetString(2)
                khachthue.isTinhTrang = rd.GetBoolean(3)
                khachthue.isTamXoa = rd.GetBoolean(4)

                listKhachThue.Add(khachthue)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listKhachThue
    End Function

    Public Shared Function TimKiemTheoTinhTrang(ByVal sqlParameter As SqlParameter) As List(Of KhachThueDTO)
        Dim listKhachThue As New List(Of KhachThueDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "TimKhachTheoTinhTrang"
            cmd.Connection = cn
            If Not IsNothing(sqlParameter) Then
                cmd.Parameters.Add(sqlParameter)
            End If
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim khachthue As KhachThueDTO = New KhachThueDTO()
                khachthue.strMaKhachThue = rd.GetString(0)
                khachthue.strHoTen = rd.GetString(1)
                khachthue.strDienThoai = rd.GetString(2)
                khachthue.isTinhTrang = rd.GetBoolean(3)
                khachthue.isTamXoa = rd.GetBoolean(4)

                listKhachThue.Add(khachthue)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try


        Return listKhachThue
    End Function
    Public Shared Sub Insert(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertKhachthue"
            cmd.Connection = cn
            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Delete(ByVal paraID As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = ""
            cmd.Connection = cn

            cmd.Parameters.Add(paraID)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Update(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateKhachthue"
            cmd.Connection = cn

            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Update_TamXoa(ByVal parameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateKhachThueTamXoa"
            cmd.Connection = cn

            cmd.Parameters.AddRange(parameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Function LayMa_KT_Cuoi() As String
        Dim macuoi As String = ""

        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayMa_KT_Cuoi"
            cmd.Connection = cn

            cn.Open()
            macuoi = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return macuoi
    End Function
End Class
